﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAO.Entity;

namespace DAO
{
    public class ImportDao
    {
        public ImportDao()
        {
            new SqlHelper();
        }

        public bool reset(DateTime untillDate)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (SqlHelper.ExecuteNonQuery("SP_RESET_SUM", CommandType.StoredProcedure,
                        "@UNTILL_DATE", untillDate,
                        "@USER_ID", LoginDao.userID))
                    {
                        sqlTransaction.Commit();
                        return true;
                    }
                    else
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        public DataTable getImEx(DateTime fromDate, DateTime toDate, string productName)
        {
            return SqlHelper.ExecuteDataTable("SP_GET_IM_EX_REPORT", CommandType.StoredProcedure,
                "@FROM_DATE", fromDate,
                "@TO_DATE", toDate,
                "@PRODUCT_NAME", productName);
        }

        public DataTable getInventory()
        {
            return SqlHelper.ExecuteDataTable("SP_GET_INVENTORY", CommandType.StoredProcedure);
        }

        public DataTable getImportReport(DateTime fromDate, DateTime todate, int kind)
        {
            return SqlHelper.ExecuteDataTable("SP_GET_IMPORT_REPORT", CommandType.StoredProcedure,
                "@FROM_DATE", fromDate,
                "@TO_DATE", todate,
                "@KIND", kind);
        }
        
        public DataRow getImportById(int importId)
        {
            DataTable table = SqlHelper.ExecuteDataTable("SP_GET_IMPORT_BY_ID", CommandType.StoredProcedure,
                "@ID", importId);
            if (table != null && table.Rows.Count >0)
            {
                return table.Rows[0];
            };
            return null;
        }

        public DataTable getImportDetails(int importId)
        {
            return SqlHelper.ExecuteDataTable("SP_GET_IMPORT_DETAILS", CommandType.StoredProcedure,
                "@ID", importId);
        }



        public bool EditImport()
        {
            return false;
        }

        public DataTable getImport(DateTime fromDate, DateTime toDate, int kind)
        {
            return SqlHelper.ExecuteDataTable("SP_GET_IMPORT", CommandType.StoredProcedure,
                "@FROM_DATE", fromDate,
                "@TO_DATE", toDate,
                "@KIND", kind);
        }

        public bool deleteImport(int id, int userId)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (!SqlHelper.ExecuteNonQuery("SP_DELETE_IMPORT", CommandType.StoredProcedure,
                       "@ID", id,
                       "@URS_ID", userId))
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }
                    else
                    {
                        if (!SqlHelper.ExecuteNonQuery("SP_DELETE_IMPORT_DETAILS", CommandType.StoredProcedure,
                        "@ID", id))
                        {
                            sqlTransaction.Rollback();
                            return false;
                        }
                        else
                        {
                            sqlTransaction.Commit();
                            return true;
                        }
                    }
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }
        public bool updateimport(int id, DateTime importAt, int importer, int reason, List<ImportDetailEntity> details)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    if (!SqlHelper.ExecuteNonQuery("SP_DELETE_IMPORT_DETAILS", CommandType.StoredProcedure,
                        "@ID", id))
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }
                    else
                    {
                        if (!SqlHelper.ExecuteNonQuery("SP_UPDATE_IMPORT", CommandType.StoredProcedure,
                            "@ID", id,
                            "@IMPORT_DATE", importAt,
                            "@USR_ID", importer))
                        {
                            sqlTransaction.Rollback();
                            return false;
                        }
                        else
                        {
                            int ImportDetailId = 0;
                            foreach (var detail in details)
                            {
                                int.TryParse(SqlHelper.getString("SP_INSERT_IMPORT_DETAIL",
                                "@IMPORT_ID", id,
                                "@PRODUCT_ID", detail.ProductId,
                                "@QUANTITY", detail.Quantity,
                                "@PRICE", detail.Price,
                                "@USR_ID", importer
                                ), out ImportDetailId);

                                if (ImportDetailId <= 0)
                                {
                                    sqlTransaction.Rollback();
                                    return false;
                                }
                            }
                            sqlTransaction.Commit();
                            return true;
                        }
                    }
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        public bool Import(DateTime importAt, int importer, int reason, List<ImportDetailEntity> details)
        {
            try
            {
                SqlTransaction sqlTransaction;
                SqlConnection con = new SqlConnection(SqlHelper.ConnectString);
                con.Open();
                sqlTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    int importId = 0;
                    int.TryParse(SqlHelper.getString("SP_INSERT_IMPORT", 
                        "@IMPORT_AT", importAt, 
                        "@IMPORTER", importer,
                        "@REASON", reason
                        ), out importId);
                    if (importId > 0)
                    {
                        int ImportDetailId = 0;
                        foreach (var detail in details)
                        {
                            int.TryParse(SqlHelper.getString("SP_INSERT_IMPORT_DETAIL",
                            "@IMPORT_ID", importId,
                            "@PRODUCT_ID", detail.ProductId,
                            "@QUANTITY", detail.Quantity,
                            "@PRICE", detail.Price,
                            "@USR_ID", importer
                            ), out ImportDetailId);

                            if (ImportDetailId <= 0)
                            {
                                sqlTransaction.Rollback();
                                return false;
                            }
                        }
                    }
                    else
                    {
                        sqlTransaction.Rollback();
                        return false;
                    }
                    sqlTransaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    sqlTransaction.Rollback();
                    Console.WriteLine(e);
                    return false;
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }
    }
}